lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-09-18（胡海峰）/2015-09-17.sql

if not exists
(select * from syscolumns where id=object_id('tb_ErpPlusPickItems') and name='Plu_OpenSingle')
begin
	alter table tb_ErpPlusPickItems add Plu_OpenSingle  nvarchar(250) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpPlusPickItems', N'COLUMN',N'Plu_OpenSingle'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接单人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpPlusPickItems', @level2type=N'COLUMN',@level2name=N'Plu_OpenSingle'
GO

if not exists
(select * from syscolumns where id=object_id('tb_ErpPlusPickItems') and name='Plu_TwoPinsCategory')
begin
	alter table tb_ErpPlusPickItems add Plu_TwoPinsCategory  nvarchar(20) NULL
end
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'tb_ErpPlusPickItems', N'COLUMN',N'Plu_TwoPinsCategory'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'二销类别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tb_ErpPlusPickItems', @level2type=N'COLUMN',@level2name=N'Plu_TwoPinsCategory'
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_ErpPlusPickItems')
BEGIN
	DROP VIEW [dbo].Vw_ErpPlusPickItems
END 
GO
create View Vw_ErpPlusPickItems
as
SELECT  tb_ErpPlusPickItems.ID
      ,Plu_OrdNumber as 订单号
      ,Plu_Amount as 金额
      ,Plu_CreateTime as 创建时间
      ,Plu_Goods as 商品名称
      ,Plu_RecordedPerson as 录单人编号
	  ,dbo.fn_CheckUserIDGetUserName(Plu_RecordedPerson) AS 录单人名称
      ,Plu_GoodsCosts as 商品成本价
      ,Plu_SourceType as 加挑金额来源类型
      ,Plu_OpenSingle as 销售人员编号
	  ,dbo.fn_CheckUserIDGetUserName(Plu_OpenSingle) AS 销售人员名称
      ,Plu_TwoPinsCategory as 二销类别编号
	  , dbo.fn_GetClassCodeToName(Plu_TwoPinsCategory, Plu_TwoPinsCategory) AS 二销类别名称
	  ,Cus_Name as 客户名称
      ,Cus_Name_py as 拼音
      ,Cus_Telephone as 电话
	  ,Ord_OrderClass as 套系类别
  FROM tb_ErpPlusPickItems
  left join tempTB_AggregationCustomer on Plu_OrdNumber=GP_OrderNumber
  left join tb_ErpOrder on Plu_OrdNumber=Ord_Number
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vw_Customer_PaymentOrders')
BEGIN
	DROP VIEW [dbo].Vw_Customer_PaymentOrders
END 
GO
create View Vw_Customer_PaymentOrders
as
select 
tb_ErpOrder.ID
,Ord_Number
,Ord_DividedShop
,Ord_Type 
,Ord_OrderClass
,Ord_PhotographyCategory
,Ord_SeriesName
,Ord_SeriesPrice
,Ord_Class
,GP_OrderNumber
,GP_CustomerGroupID
,Cus_Name
,Cus_Name_py
,Cus_Telephone
,M_Cus_CustomerNumber
,(select stuff((select ','+ OrdPe_OrdersPerson from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPersonID
,(select stuff((select ','+dbo.fn_CheckUserIDGetUserName( OrdPe_OrdersPerson) from tb_ErpOrdersPerson where OrdPe_OrderNumber=Ord_Number for xml path('')),1,1,'')) as Ord_OrdersPerson
,Ord_CreateDatetime
,(  SELECT count(id) FROM  tb_ErpOrderProductList where  OPlist_OrderNumber=Ord_Number and OPlist_PickupStatus=0) as PickupStatusCount 
from tb_ErpOrder
left join tempTB_AggregationCustomer on Ord_Number=GP_OrderNumber
GO
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_DressSaleRentalOrder')
BEGIN
	DROP VIEW [dbo].View_DressSaleRentalOrder
END 
GO
create View View_DressSaleRentalOrder
as
SELECT tb_ErpDressSaleRentalOrder.ID,Dsro_Number,Dsro_Type,Dsro_Amount,Dsro_MortgageAmount,Dsro_CustomerNumber
,Dsro_TakeDressTime,Dsro_ReservationBackTime,Dsro_Remark,Dsro_CreateDateTime,Dsro_CreateName,Dsro_UpdateDateTime,
Dsro_UpdateName,Cus_CustomerNumber,Cus_CustomizeNumber,Cus_Type,Cus_ServiceType,Cus_Grade,Cus_Name,Cus_Sex,Cus_Birthday
,Cus_BirthdayLunar,Cus_DayForMarriage,Cus_DayForMarriageLunar,Cus_Relations,Cus_QQ,Cus_MicroSignal,Cus_Telephone,Cus_FixedPhone,
Cus_Region,Cus_Address,Cus_WorkUnit,Cus_BabyWeight,Cus_BornHospital,Cus_Zodiac,Cus_CustomerSource,Cus_Status,Cus_LossReason,
Cus_DegreeOfIntent,Cus_TrackName,Cus_Remark,Cus_CreateDateTime,Cus_CreateName,Cus_UpdateDateTime,Cus_UpdateName,dbo.tb_Product(Cus_Name) as Py_Cus_Name
  ,dbo.fn_CheckUserIDGetUserName(Dsro_CreateName) as 开单人姓名
 FROM tb_ErpDressSaleRentalOrder left join tb_ErpCustomer on Dsro_CustomerNumber=Cus_CustomerNumber
GO